package com.monitor.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.monitor.entity.ErpCarTireArchives;
import com.monitor.entity.ErpTirePolice;
import com.monitor.entity.JfzRealTime;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface JfzRealTimeMapper  extends BaseMapper<JfzRealTime> {
	@Select("SELECT re.*,IFNULL(re.car_speed, 0) as carSpeed,car.car_plate,car.car_number,cu.cus_name,(SELECT count(1) FROM jinfangzhouerp.erp_customer_car WHERE jinfangzhouerp.erp_customer_car.customer_id=car.customer_id) as carNum FROM jinfangzhouerptpms.jfz_real_time as re LEFT JOIN jinfangzhouerp.erp_customer_car as car ON car.id=re.car_id LEFT JOIN jinfangzhouerp.erp_customer as cu on cu.id=car.customer_id ORDER BY re.recieve_time desc LIMIT #{page},#{limit}")
	public List<Map<String, Object>> getList(@Param("page")int page,@Param("limit")int limit);
	
	@Select("SELECT count(id) FROM jfz_real_time")
	public Integer getCount();
	
	@Select("SELECT car.id,(SELECT count(1) FROM erp_customer_car WHERE erp_customer_car.customer_id=car.customer_id) as carNum,cu.cus_name,car.car_plate,car.car_number,(CASE (SELECT count(1) FROM erp_tire_police WHERE erp_tire_police.solve_dt=NULL AND erp_tire_police.plate=car.car_plate)	WHEN 0 THEN		0	ELSE	1 END) as police_states,car.car_type3 as carType,IFNULL((SELECT car_speed FROM jfz_real_time WHERE car_id=car.id ORDER BY id DESC limit 0,1),0) as speed FROM erp_customer_car as car LEFT JOIN erp_customer as cu on cu.id=car.customer_id WHERE car.car_plate in (SELECT bi.car_plate FROM erp_bidata_list as bl LEFT JOIN erp_bidata as bi ON bl.bi_id=bi.id) ORDER BY car.car_add_date desc  LIMIT #{page},#{limit}")
	public List<Map<String, Object>> getCarList(@Param("page")int page,@Param("limit")int limit);


	@Select("SELECT car.id,(SELECT count(1) FROM erp_customer_car WHERE erp_customer_car.customer_id=car.customer_id) as carNum,cu.cus_name,car.car_plate,car.car_number,(CASE (SELECT count(1) FROM erp_tire_police WHERE erp_tire_police.solve_dt=NULL AND erp_tire_police.plate=car.car_plate)\tWHEN 0 THEN\t\t0\tELSE\t1 END) as police_states,car.car_type3 as carType,IFNULL((SELECT car_speed FROM jfz_real_time WHERE car_id=car.id ORDER BY id DESC limit 0,1),0) as speed FROM erp_customer_car as car LEFT JOIN erp_customer as cu on cu.id=car.customer_id WHERE car.car_plate in (SELECT bi.car_plate FROM erp_bidata_list as bl LEFT JOIN erp_bidata as bi ON bl.bi_id=bi.id  AND bi.cus_id in (${custId})) ${sql} ORDER BY car.customer_id asc  ")
	public List<Map<String, Object>> getCarListById(@Param("custId")String custId,@Param("sql")String sql);

	@Select("SELECT IFNULL(car_speed,0) as speed FROM jfz_real_time WHERE car_id=#{carId} ORDER BY id DESC LIMIT 0,1")
	public Double selectSpeedByCarId(@Param("carId")int carId);
	
	@Select("SELECT IFNULL(voltage,0) as  voltage,IFNULL(temperature,0) as temperature,IFNULL(tire_pressure,0) as tire_pressure,state FROM jfz_real_time WHERE car_id=#{carId} AND tire_position=#{position} ORDER BY id DESC LIMIT 0,1")
	public Map<String, Object> selectCarByIdAndPosition(@Param("carId")int carId,@Param("position")String position);
	
	@Select("SELECT recieve_time FROM jfz_real_time WHERE car_id=#{carId} ORDER BY id DESC LIMIT 0,1")
	public String selectTimeByCarId(@Param("carId")int carId);
	
	@Select("SELECT DISTINCT car_id,tire_location FROM erp_tire_police WHERE solve_dt=NULL")
	public List<Map<String, Object>> selectStateErrorCar();
	
	@Select("SELECT plate,tire_location,police_type FROM erp_tire_police  WHERE  car_id=#{carId} AND tire_location=#{position} ORDER BY id DESC limit 0,1")
	public Map<String, Object> getErrorCarList(@Param("position")String position,@Param("carId")int carId);
	
	//获取carId的所有胎位
	@Select("SELECT tire_position FROM jfz_real_time WHERE car_id=#{carId} GROUP BY tire_position ORDER BY tire_position")
	public List<String> selectPositionByCarId(@Param("carId")int carId);
	
	//获取carId指定胎位的温度和时间
	@Select("SELECT  FORMAT(temperature,2) as temperature,recieve_time FROM jfz_real_time WHERE tire_position=#{position} AND car_id=#{carId} ${sql}")
	public List<Map<String, Object>> selectTemperatureByCarIdAndPosition(@Param("position")String position,@Param("carId")int carId,@Param("sql")String sql);
	
	//获取carId指定胎位的胎压和时间
	@Select("SELECT tire_pressure,recieve_time FROM jfz_real_time WHERE tire_position=#{position} AND car_id=#{carId} ${sql}")
	public List<Map<String, Object>> selectVoltageByCarIdAndPosition(@Param("position")String position,@Param("carId")int carId,@Param("sql")String sql);
		
	//获取carId指定胎位的速度和时间
	//SELECT DISTINCT  AVG(car_speed) as car_speed,DATE_FORMAT(recieve_time,'%Y-%m-%d %H:00:00') as recieve_time FROM jfz_real_time WHERE car_id=20 GROUP BY recieve_time  每小时的平均速度
	@Select("SELECT car_speed,recieve_time FROM jfz_real_time WHERE car_id=#{carId} ${sql}")
	public List<Map<String, Object>> selectSpeedByCarIdAndPosition(@Param("carId")int carId,@Param("sql")String sql);
	
	//查询轮胎信息
	@Select("SELECT * FROM  erp_car_tire_archives AS cta  WHERE cta.car_id=#{carId} AND cta.tire_location=#{position} ORDER BY id desc limit 0,1")
	public ErpCarTireArchives selectTireArchives(@Param("carId")int carId,@Param("position")String position);
	
	//查询轮胎传感器和定位器id
	@Select("SELECT cta.sensors_id,cta.positioner_id FROM erp_car_tire_archives as cta WHERE cta.car_id=#{carId} AND cta.tire_location=#{position} order by id desc limit 0,1")
	public Map<String, Object> selectSensorsAndPositioner(@Param("carId")int carId,@Param("position")String position);
	
	@Select("SELECT recieve_time FROM jfz_real_time WHERE car_id=#{carId} ${sql}")
	public List<String> selectRecieveTimeByCarId(@Param("carId")int carId,@Param("sql")String sql);
	
	//车辆信息列表
	@Select("select * FROM (SELECT	c.id,c.bz,(SELECT erp_customer.cus_name FROM erp_customer WHERE erp_customer.id=c.customer_id) as customer_name,c.car_plate,c.car_number,c.car_brand,c.car_model,c.car_gz_date,(SELECT j.specification FROM erp_inspection_content j WHERE j.id IN ( SELECT MIN(i.id) FROM erp_inspection_content i GROUP BY i.customer_id HAVING i.customer_id =c.car_plate )) as guige,d.driver_a,d.driver_a_phone,d.driver_b,d.driver_b_phone,dr.supervisor_name,dr.supervisor_phone,dr.personnel_name,dr.personnel_phone FROM	erp_customer_car c LEFT JOIN	erp_driver as d ON 	d.car_number=c.car_plate LEFT JOIN	erp_driver_repair as dr ON dr.pid=c.customer_id WHERE	c.customer_id in (${cid}) )  qqqq  ORDER BY  qqqq.customer_name ASC , qqqq.car_number asc,qqqq.car_plate asc")
	public List<Map<String, Object>> selectByCustomer(@Param("cid")String cid);
	
	//统计分析 车辆查询
	@Select("SELECT c.id,cc.id as carId,(SELECT COUNT(1) FROM erp_customer_car WHERE erp_customer_car.customer_id=c.id) as car_num,c.cus_name,cc.car_plate FROM erp_customer_car as cc LEFT JOIN  erp_customer as c  ON cc.customer_id=c.id WHERE c.id=#{cId} limit #{page},#{limit}")
	public List<Map<String, Object>> selectTableByCid(@Param("cid")Integer cid,@Param("page")int page,@Param("limit")int limit);

	//统计分析 车辆查询条数
	@Select("SELECT count(1) FROM erp_customer_car as cc LEFT JOIN  erp_customer as c  ON cc.customer_id=c.id WHERE c.id=#{cId}")
	public Integer selectTableByCidCount(@Param("cid")Integer cid);

	//报警管理 列表查询
	@Select("SELECT p.*,c.cus_name,dr.supervisor_name,dr.supervisor_phone,dr.personnel_name,dr.personnel_phone FROM erp_tire_police as p LEFT JOIN erp_customer_car as cc ON p.plate=cc.car_plate LEFT JOIN erp_customer as c ON c.id=cc.customer_id LEFT JOIN erp_driver_repair as dr ON dr.pid=c.id WHERE c.id=#{cId} ${sql}  ORDER BY id DESC limit #{page},#{limit}")
	public List<Map<String, Object>> selectErrorListByCid(@Param("cId")Integer cId,@Param("sql")String sql,@Param("page")int page,@Param("limit")int limit);
	
	@Select("SELECT count(1) FROM erp_tire_police as p LEFT JOIN erp_customer_car as cc ON p.plate=cc.car_plate LEFT JOIN erp_customer as c ON c.id=cc.customer_id LEFT JOIN erp_driver_repair as dr ON dr.pid=c.id WHERE c.id=#{cId} ${sql} ")
	public Integer selectErrorListByCidCount(@Param("cId")Integer cId,@Param("sql")String sql);
	
	//统计分析 查询正常情况下统计分析的数量和时长
	@Select("SELECT count(1) as num,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(#{startTime},'%Y-%m-%d %H:%i')),(DATE_FORMAT(#{endTime}, '%Y-%m-%d %H:%i'))) as time FROM jfz_real_time as r WHERE car_id=#{carId}")
	public Map<String, Object> normalStatistics(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("carId")int carId);
	
	//统计分析 查询正常情况下时间范围内所有数据
	@Select("SELECT recieve_time FROM jfz_real_time WHERE car_id=#{carId} ${sql}")
	public List<String>  selectTimeScope(@Param("carId")int carId,@Param("sql")String sql);
	
	//统计分析 查询报警范围在10-15之间的数据
	@Select("SELECT * FROM erp_tire_police WHERE car_id=#{carId} HAVING (police_value-normal_value)>(normal_value*#{min}) AND (police_value-normal_value)<(normal_value*#{max})")
	public List<ErpTirePolice> selectPoliceList(@Param("carId")int carId,@Param("min")Double min,@Param("max")Double max);

	//统计分析 查询异常情况下10-15%统计分析的数量和时长
	@Select("SELECT count(1) as num,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(#{startTime},'%Y-%m-%d %H:%i')),(DATE_FORMAT(#{endTime}, '%Y-%m-%d %H:%i'))) as time FROM erp_tire_police as r WHERE car_id=#{carId}")
	public Map<String, Object> errorStatistics(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("carId")int carId);

	//统计分析 查询报警范围在15以上的数据
	@Select("SELECT * FROM erp_tire_police WHERE car_id=#{carId} HAVING (police_value-normal_value)<(normal_value*#{max})")
	public List<ErpTirePolice> selectPoliceList1(@Param("carId")int carId,@Param("max")Double max);


	//胎压监控-报警管理, 胎位  	,@Param("carId")Integer carId 暂时没加carId查询所有
	@Select("SELECT COUNT(1) 'value' ,tire_location 'name' FROM erp_tire_police WHERE  police_dt>= #{startTime} and police_dt<= #{endTime} GROUP BY tire_location")
	public  List<Map<String, Object>> selectLocation(@Param("startTime")String startTime,@Param("endTime")String endTime);

	//胎压监控-报警管理, 报警类型  //,@Param("carId")Integer carId  同下
	@Select("SELECT COUNT(1) 'value' ,police_type 'name' FROM erp_tire_police WHERE  police_dt>= #{startTime} and police_dt<= #{endTime} GROUP BY police_type ")
	public   List<Map<String, Object>> selectType (@Param("startTime")String startTime,@Param("endTime")String endTime);

	//胎压监控-报警管理, 报警原因			,@Param("carId")Integer carId 暂时没加carId查询所有
	@Select("SELECT COUNT(1) 'value' ,reason 'name' FROM erp_tire_police WHERE  police_dt>= #{startTime} and police_dt<= #{endTime}GROUP BY reason  ")
	public   List<Map<String, Object>> selectReason (@Param("startTime")String startTime,@Param("endTime")String endTime);






}
